--Function of adding decimal column
create or replace procedure meta_add_decimal_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);
    
    a_table_name :=  p_get_table_name(a_table_id);
    
    --Changing the metadata
    insert into meta_decimal_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (a_column_id, a_table_id, a_column_name,
        p_generate_order_index(a_table_id), 1);     

    --Dropping table view
    p_drop_view_if_exists(a_table_name);
     
     execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add ' || a_column_name
    || ' number';

    --Creating table view
    p_create_table_view(a_table_id);

end;

--go

--Function of setting decimal column minimum
create or replace procedure meta_set_decimal_minimum(
    --Column id
    a_column_id raw,
    --Minimum
    a_minimum number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old minimum
    a_old_minimum number;
    --Column type
    a_column_type varchar2(30);
begin
    p_check_col_type(a_column_id, 'meta_decimal_columns', 'decimal');
    
    p_set_numeric_minimum(a_column_id, a_minimum);
end;

--go

--Function of setting numeric column minimum
create or replace procedure p_set_numeric_minimum(
    --Column id
    a_column_id raw,
    --Minimum
    a_minimum number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old minimum
    a_old_minimum number;
    --Column type
    a_column_type varchar2(30);
begin    
    --Reading column type
    select column_type
    into a_column_type
    from meta_table_columns
    where column_id = a_column_id;
    
    --Reading column properties
    execute immediate 'select table_id, column_name, minimum'
    || ' from ' || a_column_type
    || ' where column_id = :1'    
    into a_table_id, a_column_name, a_old_minimum
    using a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
     
     --There is no need to drop table view
     
     if a_old_minimum is not null then
         --Drop old constraint 
         execute immediate 'alter table tbl_' 
         || a_table_name
         || ' drop constraint n' || substr(a_column_id, 1, 29);
     end if;
     
     --Create new check
     if a_minimum is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' add constraint n' || substr(a_column_id, 1, 29)
        || ' check(' || a_column_name
        || ' >= ' || to_char(a_minimum,  '99999999999999999999999999999.999999') || ')';
     end if;
     
     --Updating the metadata
     if a_minimum is not null then
        execute immediate 'update ' || a_column_type
        || ' set minimum = :1 where column_id = :2'
        using a_minimum, a_column_id;
    else
         execute immediate 'update ' || a_column_type
        || ' set minimum = null'
        || ' where column_id = :1'
        using a_column_id;
    end if;
end;

--go

--Function of setting decimal column maximum
create or replace procedure meta_set_decimal_maximum(
    --Column id
    a_column_id raw,
    --Maximum
    a_maximum number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old maximum
    a_old_maximum number;
    --Column type
    a_column_type varchar2(30);
begin
    p_check_col_type(a_column_id, 'meta_decimal_columns', 'decimal');
    
    p_set_numeric_maximum(a_column_id, a_maximum);
end;

--go

--Function of setting numeric column maximum
create or replace procedure p_set_numeric_maximum(
    --Column id
    a_column_id raw,
    --Maximum
    a_maximum number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old maximum
    a_old_maximum number;
    --Column type
    a_column_type varchar2(30);
begin    
    --Reading column type
    select column_type
    into a_column_type
    from meta_table_columns
    where column_id = a_column_id;

    --Reading column properties
    execute immediate 'select table_id, column_name, maximum'
    || ' from ' || a_column_type
    || ' where column_id = :1'    
    into a_table_id, a_column_name, a_old_maximum
    using a_column_id;
        
    a_table_name := p_get_table_name(a_table_id);
    
    --There is no need to drop table view
         
    if a_old_maximum is not null then
        --Drop old constraint 
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' drop constraint x' || substr(a_column_id, 1, 29);
    end if;
         
    --Create new check
    if a_maximum is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' add constraint x' || substr(a_column_id, 1, 29)
        || ' check(' || a_column_name
        || ' <= ' || to_char(a_maximum,  '99999999999999999999999999999.999999') || ')';
    end if;
    
    --Updating the metadata
    if a_maximum is not null then
        execute immediate 'update ' || a_column_type
        || ' set maximum = :1 where column_id = :2'
        using a_maximum, a_column_id;
    else
         execute immediate 'update ' || a_column_type
        || ' set maximum = null'
        || ' where column_id = :1'
        using a_column_id;
    end if;
end;

--go

--Function of setting decimal column precision
create or replace procedure meta_set_decimal_precision(
    --Column id
    a_column_id raw,
    --Precision
    a_precision number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old precision
    a_old_precision number(3);
    --Old scale
    a_old_scale number(3);
begin
    p_check_col_type(a_column_id, 'meta_decimal_columns', 'decimal');
    
    --Reading column properties
    select table_id, column_name, precision, scale
    into a_table_id, a_column_name, a_old_precision, a_old_scale
    from meta_decimal_columns
    where column_id = a_column_id;
    
    --If there is no need for change
    if (a_precision = a_old_precision) 
        or (a_precision is null and a_old_precision is null) then
        return;
    end if;
         
    a_table_name := p_get_table_name(a_table_id);
    
    --Changing the metadata
    update meta_decimal_columns
    set precision = a_precision
    where column_id = a_column_id;
         
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
    --If precision is not specified
    if a_precision is null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify(' || a_column_name
        || ' numeric)';
    --If scale is not specified
    elsif a_old_scale is null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify(' || a_column_name
        || ' numeric(' || a_precision || '))';
    --If precision and scale are specified
    elsif a_old_scale is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify(' || a_column_name
        || ' numeric(' || a_precision || ',' || a_old_scale || '))';
    end if;
         
    --Creating table view
    p_create_table_view(a_table_id);

end;

--go

--Function of setting decimal column scale
create or replace procedure meta_set_decimal_scale(
    --Column id
    a_column_id raw,
    --Scale
    a_scale number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Old precision
    a_old_precision number(3);
    --Old scale
    a_old_scale number(3);
begin
    p_check_col_type(a_column_id, 'meta_decimal_columns', 'decimal');
    
    --Reading column properties
    select table_id, column_name, precision, scale
    into a_table_id, a_column_name, a_old_precision, a_old_scale
    from meta_decimal_columns
    where column_id = a_column_id;
    
    --If there is no need for change
    if (a_scale = a_old_scale) 
        or (a_scale is null and a_old_scale is null) then
        return;
    end if;
    
    a_table_name := p_get_table_name(a_table_id);
    
    --Changing the metadata
    update meta_decimal_columns
    set scale = a_scale
    where column_id = a_column_id;
         
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
    --If scale is not specified
    if (a_old_precision is not null) and (a_scale is null) then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify(' || a_column_name
        || ' numeric(' || a_old_precision || '))';
    --If precision and scale are specified
    elsif  (a_old_precision is not null) and (a_scale is not null) then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify(' || a_column_name
        || ' numeric(' || a_old_precision || ',' || a_scale || '))';
    end if;
         
    --Creating table view
    p_create_table_view(a_table_id);
    
end;

--go

--Function of setting column default value
create or replace procedure meta_set_decimal_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Column type
    a_column_type varchar2(30);
begin
    p_check_col_type(a_column_id, 'meta_decimal_columns', 'decimal');
    
    p_set_numeric_default(a_column_id, a_default_value);
end;

--go

--Function of setting numeric default value
create or replace procedure p_set_numeric_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Column type
    a_column_type varchar2(30);
begin    
    --Reading column type
    select column_type
    into a_column_type
    from meta_table_columns
    where column_id = a_column_id;
    
    --Reading column properties
    execute immediate 'select table_id, column_name'
    || ' from ' || a_column_type
    || ' where column_id = :1'
    into a_table_id, a_column_name
    using a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
    
     if a_default_value is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default ' || to_char(a_default_value,  '99999999999999999999999999999.999999') || ')';
    else
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default null)';
    end if;
    
    --Updating the metadata
    if a_default_value is not null then
        execute immediate 'update ' || a_column_type
        || ' set default_value = :1 where column_id = :2'
        using a_default_value, a_column_id;
    else
        execute immediate 'update ' || a_column_type
        || ' set default_value = null'
        || ' where column_id = :1'
        using a_column_id;
    end if;
end;

--go